package Mapper;

import Utils.JdbcUtils;
import Utils.ThreadLocalUtis;
import pojo.Bill;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class InputBillMapper {
    private Bill bill;
    private ThreadLocalUtis threadLocalUtis = new ThreadLocalUtis();
    private JdbcUtils jdbcUtils = new JdbcUtils();

    public int storage(Bill bill) throws SQLException {
        String sql = "insert into inputbill (billid, billname, money, date, billtype, comment, userid) VALUES (?,?,?,?,?,?,?)";
        PreparedStatement prepareStatement = null;
        Connection conn = jdbcUtils.getConn();
        int i = 0;
        try {
            conn.setAutoCommit(false);
            prepareStatement = jdbcUtils.getPrepareStatement(sql);
            prepareStatement.setInt(1, bill.getBillId());
            prepareStatement.setString(2, bill.getBillName());
            prepareStatement.setDouble(3, bill.getMoney());
            prepareStatement.setObject(4, bill.getDate());
            prepareStatement.setString(5, bill.getBillType());
            prepareStatement.setString(6, bill.getComment());
            Map<String, Object> map = ThreadLocalUtis.get();
            int userid = (Integer) map.get("userid");
            prepareStatement.setInt(7, userid);
            i = prepareStatement.executeUpdate();
        } catch (SQLException e) {
            conn.rollback();
        } finally {
            try {
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
            }
        }

        return i;
    }

    public List<Bill> getInputBills() {
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid = (Integer) map.get("userid");
        String sql = "select * from inputbill where userid = ?";
        List<Bill> bills = null;
        try {
            PreparedStatement prepareStatement = jdbcUtils.getPrepareStatement(sql);
            prepareStatement.setInt(1, userid);
            ResultSet resultSet = prepareStatement.executeQuery();
            bills = new ArrayList<>();
            while (resultSet.next()) {
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date date = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, date, billType, comment, userid);
                bills.add(bill);
            }

        } catch (SQLException e) {
            e.getStackTrace();
        }
        return bills;
    }

    public List<Bill> getInputBillForTime(Date date) {
        String sql = "select * from  inputbill where inputbill.date   = ? and userid = ?";
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid = (Integer) map.get("userid");
        List<Bill> bills = null;
        try {
            PreparedStatement prepareStatement = jdbcUtils.getPrepareStatement(sql);
            java.sql.Date sqlDate = new java.sql.Date(date.getTime());
            prepareStatement.setDate(1, sqlDate);
            prepareStatement.setInt(2, userid);
            ResultSet resultSet = prepareStatement.executeQuery();
            bills = new ArrayList<>();
            System.out.println(resultSet);
            while (resultSet.next()) {
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
            System.out.println(bill);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return bills;
    }

    public List<Bill> getInputAndOutputBill(String date) {
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid = (Integer) map.get("userid");
        String sql = "select  * from\n" +
                "              inputbill ,\n" +
                "              outputbill\n" +
                "          where  inputbill.date  = outputbill.date\n" +
                "            and inputbill.date = '" + date + "'\n" +
                "and outputbill.UserId =inputbill.userid  and inputbill.userid\n" +
                "= " + userid + "";
        List<Bill> bills = null;
        try {
            Statement statement = jdbcUtils.getStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            bills = new ArrayList<>();
            System.out.println(resultSet);
            while (resultSet.next()) {
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return bills;
    }
    /*查询月度账单*/
    public List<Bill> getMonthBill(){
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
         Map  <String ,  Object> map = ThreadLocalUtis.get();
       int userid =   (Integer) map.get("userid");
        String sql =  "select * from inputbill where  year(date)="+year+" and   month(date)  =  "+month+"";
        List<Bill> bills = null;
        try {
            Statement statement = jdbcUtils.getStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            bills = new ArrayList<>();
            while (resultSet.next()){
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            try {
                jdbcUtils.getConn().rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
        return  bills;
    }
    public List<Bill> getMonthBill(int year,  int month){
        Map  <String ,  Object> map = ThreadLocalUtis.get();
        int userid =   (Integer) map.get("userid");
        String sql =  "select * from inputbill where  year(date)="+year+" and   month(date)  =  "+month+"";
        List<Bill> bills = null;
        try {
            Statement statement = jdbcUtils.getStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            bills = new ArrayList<>();
            while (resultSet.next()){
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            try {
                jdbcUtils.getConn().rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
        return  bills;
    }
}
